var express = require('express');
var dayjs = require('dayjs')
const path = require('path')
const fs = require('fs');
const formidable = require('formidable')
const sqlQuery = require('./../db/resetSet/index.js');

var router = express.Router();

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
});

// 查询班级
router.get('/getDepartment',(req, res)=>{
  const sql = `select * from department`;
  sqlQuery(sql,(data)=>{
    if(data.length == 0){
      res.send({
        code:"2001",
        message:"暂无班级"
      })
    }else{
      res.send({
        code:"200",
        message:"查询班级成功",
        data
      })
    }
  })
})

// 获取用户信息
router.get('/getUserInfo',(req,res)=>{
  const { user_name, department_id } = req.query
  const sql = `select * from user_info where user_name='${user_name}' and department_id='${department_id}'`;
  sqlQuery(sql,(data)=>{
    if(data.length == 0){
      res.send({
        code:"2001",
        message:"暂无用户信息"
      })
    }else{
      res.send({
        code:"200",
        message:"获取用户信息成功",
        data
      })
    }
  })
})

// 登录：通过用户名和班级查找数据库-> 如果此学生没有在数据库中，则注册学生信息，否则不注册 直接返回学生信息
router.get('/login',(req, res)=>{
  const { user_name, department_id } = req.query
  const sql = `select * from user_info where user_name='${user_name}' and department_id='${department_id}'`;
  sqlQuery(sql,(data)=>{
    if(data.length == 0){
        const nowTime = dayjs().valueOf()
        const sql_insert = `insert into user_info values(null,'${user_name}','${department_id}','${nowTime}')`;
        sqlQuery(sql_insert,(data,err)=>{
            if(data){
              res.send({
                code:"200",
                message:"注册成功,并自动登录"
              })
            }else if(err){
              res.send(err)
            }
        })
    }else{
      res.send({
        code:"200",
        message:"登录成功"
      })
    }
  })
})

// 获取个人打卡信息 总数
router.get('/getClockListAll',(req,res)=>{
  const { user_name, department_id } = req.query
  const sql =`select * from clock where user_id=(SELECT id from user_info WHERE user_name='${user_name}' and department_id='${department_id}')`
  sqlQuery(sql,(data)=>{
    if(data.length == 0){
      res.send({
        code:"2001",
        message:"暂无打卡总数信息",
      })
    }else{
      res.send({
        code:"200",
        message:"查询打卡信息总数成功",
        data:{ total:data.length }
      })
    }
  })
})

// 获取个人打卡信息 分页
router.get('/getClockList',(req,res)=>{
  const { user_name, user_id, department_id, currentPage, pageSize } = req.query
  const sql =`select * from clock where user_id=(SELECT id from user_info WHERE user_name='${user_name}' and department_id='${department_id}') limit ${(currentPage-1)*pageSize},${pageSize}`
  sqlQuery(sql,(data)=>{
    if(data.length == 0){
      res.send({
        code:"2001",
        message:"暂无打卡信息"
      })
    }else{
      // 获取图片表
      const sql_pic =`select * from picture where user_id=${user_id}`
      sqlQuery(sql_pic,(data_pic)=>{
        if(data_pic.length == 0){
          res.send({
            code:"200",
              message:"查询打卡信息成功",
              data
          })
        }else{
          data.map(item=>{
            item.clock_url = data_pic.filter(item_pic=>item_pic.user_id == item.user_id && item_pic.id==item.picture_id)[0]?.picture_src || ''
          })
  
          res.send({
            code:"200",
              message:"查询打卡信息成功",
              data
          })
        }
      })
    }
  })
})

// 上传照片,存入服务器static目录中,并把图片写入图片数据库
router.post('/pictureUpload', async(req, res, next)=> {
  const { department, user_id, url } = req.body
  // https://juejin.cn/post/6844904084693073927
  
  const nowTime = dayjs().valueOf()
  const base64 = url.replace(/^data:image\/\w+;base64,/,""); //去掉图片base64码前面部分data:image/png;base64
  const picSrc = path.join(__dirname, "../public/images", `${department}-${user_id}-${nowTime}.png`);
  // new Buffer 操作权限太大，v6.0后使用Buffer.from()创建构造函数
  const dataBuffer = new Buffer(base64, 'base64'); //把base64码转成buffer对象，
  // 用fs写入文件到服务器磁盘
  fs.writeFile(picSrc, dataBuffer, function(err){
    if(err){
      res.send({
        code:"2001",
        message:"图片上传失败"
      })
    }else{
      // 写入数据库
      const clock_url = `http://${req.headers.host}/images/${department}-${user_id}-${nowTime}.png`
      const sql =`insert into picture values(null,'${clock_url}','${user_id}','${nowTime}')`
      sqlQuery(sql,(data)=>{
        if(data.length == 0){
          res.send({
            code:"2001",
            message:"图片上传失败"
          })
        }else{
          res.send({
            code:"200",
            message:"图片上传成功",
            data:clock_url
          })
        }
      })
    }
  })
})

// 打卡（需要先等上面 图片上传完在打卡）
router.get('/clockIn',(req,res)=>{
  const { user_id, department_id, clock_url } = req.query
  const nowTime = dayjs().valueOf()
  // 查询图片表的图片id
  const sql_pic =`select id from picture where user_id=${user_id} and picture_src='${clock_url}'`
  sqlQuery(sql_pic,(data_pic)=>{
    // 插入打卡记录
    const sql =`insert into clock values(null,${user_id},${department_id},${data_pic[0].id},${nowTime})`
    sqlQuery(sql,(data)=>{
      console.log(data);
      if(data.length == 0){
        res.send({
          code:"2001",
          message:"打卡失败"
        })
      }else{
        res.send({
          code:"200",
          message:"打卡成功",
        })
      }
    })
  })
})

module.exports = router;